package edu.zju.gis.dbfg.server.util;

import edu.zju.gis.dbfg.server.model.ExternalDataArgs;
import edu.zju.gis.dbfg.server.model.vo.FeatureRecord;
import edu.zju.gis.dbfg.server.model.vo.tableRecordsWithTotalsize;
import org.json.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.JSONValue;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PgHelper extends ExternalHelper {
    public JSONObject testConnection(ExternalDataArgs sourceConnect) throws Exception {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName();
        this.connection = DriverManager.getConnection(url,
                sourceConnect.getUserName(), sourceConnect.getPassword());
        System.out.println("Opened database successfully");
        return this.res;
    }

    public List<JSONObject> queryConstruct(ExternalDataArgs sourceConnect) throws Exception {
        List<JSONObject> resObject = new ArrayList<>();
        if (this.connection == null || this.connection.isClosed()) {
            testConnection(sourceConnect);
        }
        Statement stmt = this.connection.createStatement();
        if (sourceConnect.getSchemaName() == null) {
            //获取schema列表
            String selectScheSql = "select * from information_schema.schemata";
            resObject = querySql(stmt, selectScheSql, "schema_name");
        } else if (sourceConnect.getTableName() == null) {
            //获取表名列表
            String selectSql = "select * from pg_tables where schemaname ='" + sourceConnect.getSchemaName() + "'";
            resObject = querySql(stmt, selectSql, "tablename");
        } else if (sourceConnect.getFields() == null) {
            String sqlfields = "select * from information_schema.columns   where table_schema='" + sourceConnect.getSchemaName() + "' and table_name='" + sourceConnect.getTableName() + "'";
            resObject = querySql(stmt, sqlfields, "column_name");
        }
        return resObject;
    }

    public tableRecordsWithTotalsize pageSearh(ExternalDataArgs sourceConnect, int pageNo, int pageSize) throws Exception {
        tableRecordsWithTotalsize tableRecordsWithTotalsize = new tableRecordsWithTotalsize();
        if (this.connection == null || this.connection.isClosed()) {
            testConnection(sourceConnect);
        }
        Statement stmt = this.connection.createStatement();
        //获取字段
        List<JSONObject> jsonObjects = queryConstruct(sourceConnect);
        String colName = jsonObjects.get(0).get("value").toString();
        //从第3条开始，取10条
//                select * from testwmx order by name desc limit 10 offset 3
        int offset = (pageNo - 1) * pageSize;
        String sql = "";
        //有空间字段,返回空间字段的geojson格式
        if (!sourceConnect.getSpatialField().equals("none")) {
            sql = "select *,ST_AsGeoJSON(" + sourceConnect.getSpatialField() + ") from " + sourceConnect.getTableName() + " order by " + colName + " desc limit " + pageSize + " offset " + offset;
        } else {
            sql = "select * from " + sourceConnect.getTableName() + " order by " + colName + " desc limit " + pageSize + " offset " + offset;
        }
        String sqlAll = "select * from " + sourceConnect.getTableName();
        tableRecordsWithTotalsize = search(stmt, sql, sqlAll, sourceConnect.getSpatialField());
        return tableRecordsWithTotalsize;
    }


    public FeatureRecord identifyQuery(String lon, String lat, ExternalDataArgs sourceConnect) throws Exception {
        FeatureRecord featureRecord = new FeatureRecord();
        //根据sourceConnect得出坐标系
        if (this.connection == null || this.connection.isClosed()) {
            testConnection(sourceConnect);
        }
        Statement stmt = this.connection.createStatement();
        String crsSql = "select st_srid(" + sourceConnect.getSpatialField() + ") from " + sourceConnect.getTableName() + " limit 1";
        List<JSONObject> sridJson = querySql(stmt, crsSql, "st_srid");
        String srid = sridJson.get(0).get("value").toString();
        //然后进行查询
        Statement stmt2 = this.connection.createStatement();
        String identifySql = "select *,ST_AsGeoJSON("+sourceConnect.getSpatialField()+") from " + sourceConnect.getTableName() + " where ST_Within(ST_GeomFromText('POINT(" + lon + " " + lat + ")', " + srid + ")," + sourceConnect.getSpatialField() + ")";
        ResultSet rs = stmt2.executeQuery(identifySql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        String[] name = new String[count];
        for (int i = 0; i < count; i++) {
            name[i] = rsmd.getColumnName(i + 1);
        }
        Map<String, Object> att = new HashMap<>();
        while (rs.next()) {
            for (int i = 0; i < count; i++) {
                String key = name[i];
                if (key.equals(sourceConnect.getSpatialField())) {
//                    featureRecord.setGeom(rs.getString(key));
                } else if(key.equals("st_asgeojson")){
                    JSONArray features=new JSONArray();
                    JSONObject feature=new JSONObject();
                    JSONObject jsonObjectGeometry=(JSONObject) JSONValue.parse(rs.getString(key));
                    feature.put("geometry",jsonObjectGeometry);
                    features.put(feature);
                    JSONObject jsonObjectGeom=new JSONObject();
                    jsonObjectGeom.put("features",features);
                    featureRecord.setGeom(jsonObjectGeom.toString());
                }else{
                    att.put(key, rs.getString(key));
                }
            }
        }
        featureRecord.setAtt(att);
        rs.close();
        stmt2.close();
        return featureRecord;
    }

    public List<FeatureRecord> areaQuery(JSONArray areaArray, ExternalDataArgs sourceConnect)throws Exception {
        //参数为前后闭合的polygon

        List<FeatureRecord> featureRecords = new ArrayList<>();
        //根据sourceConnect得出坐标系
        if (this.connection == null || this.connection.isClosed()) {
            testConnection(sourceConnect);
        }
        Statement stmt = this.connection.createStatement();
        String crsSql = "select st_srid(" + sourceConnect.getSpatialField() + ") from " + sourceConnect.getTableName() + " limit 1";
        List<JSONObject> sridJson = querySql(stmt, crsSql, "st_srid");
        String srid = sridJson.get(0).get("value").toString();
        //然后进行查询

        String area="";
        for(int i=0;i<areaArray.length();i++){
           area=area+areaArray.getJSONObject(i).get("lon")+" "+areaArray.getJSONObject(i).get("lat")+",";
        }
        String identifySql = "select *,ST_AsGeoJSON("+sourceConnect.getSpatialField()+") from " + sourceConnect.getTableName() + " where ST_Intersects(ST_GeomFromText('polygon((" + area.substring(0,area.length()-1)+"))', " + srid + ")," + sourceConnect.getSpatialField() + ")";
        Statement stmt2 = this.connection.createStatement();
        ResultSet rs = stmt2.executeQuery(identifySql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        String[] name = new String[count];
        for (int i = 0; i < count; i++) {
            name[i] = rsmd.getColumnName(i + 1);
        }
        Map<String, Object> att = new HashMap<>();

        while (rs.next()) {
            FeatureRecord featureRecord=new FeatureRecord();
            for (int i = 0; i < count; i++) {
                String key = name[i];
                if (key.equals(sourceConnect.getSpatialField())) {
//                    featureRecord.setGeom(rs.getString(key));
                } else if(key.equals("st_asgeojson")){
                    JSONArray features=new JSONArray();
                    JSONObject feature=new JSONObject();
                    JSONObject jsonObjectGeometry=(JSONObject) JSONValue.parse(rs.getString(key));
                    feature.put("geometry",jsonObjectGeometry);
                    features.put(feature);
                    JSONObject jsonObjectGeom=new JSONObject();
                    jsonObjectGeom.put("features",features);
                    featureRecord.setGeom(jsonObjectGeom.toString());
                }else{
                    att.put(key, rs.getString(key));
                }
            }
            featureRecord.setAtt(att);
            featureRecords.add(featureRecord);
        }
        rs.close();
        stmt2.close();
        return featureRecords;

    }

}
